# _*_ coding:utf-8 _*_
# @File  : spot_fill.py
# @Time  : 2022-03-24
# @Author: zizle
import datetime
import pandas as pd
from typing import Optional, List
from fastapi import APIRouter, Header, Query, Body

from hutool import security
from db import FAConnection
from v1_all_api import constant
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse

spot_fill_api = APIRouter()


@spot_fill_api.post('/')  # 添加现货价格数据(一个或多个)
async def save_spot_fill(ruizy_token: str = Header(...), spot_data: Optional[List] = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 处理数据入库
    df = pd.DataFrame(spot_data)
    # df = df[df['price'] > 0]
    df = df[~((df['price'] == 0) & (df['src_note'] == '-'))]  # 去除价格是0且备注为-的数据
    df['price'] = df['price'].astype(float)
    # 去除价格为0的数据
    df = df[df['price'] > 0]
    if df.empty:
        return AllResponse.operate_successfully(msg='保存现货价格数据成功无新增数据!')
    user_id = person['uid']
    df['spot_ts'] = df['spot_date'].apply(lambda x: int(datetime.datetime.strptime(x, '%Y-%m-%d').timestamp()))
    df['creator'] = [user_id for _ in range(df.shape[0])]
    del df['variety_name']
    del df['spot_date']
    save_data = df.to_dict(orient='records')
    save_sql = 'INSERT IGNORE INTO dat_spot_price (creator,spot_ts,variety_en,' \
               'price,src_note) ' \
               'VALUES (%(creator)s,%(spot_ts)s,%(variety_en)s,' \
               '%(price)s,%(src_note)s);'
    db = FAConnection(conn_name='Save Spots')
    count, _ = db.insert(save_sql, param=save_data, many=True, keep_conn=True)  # 先插入一波
    db.correct_table_auto_increment(table_name='dat_spot_price')
    update_sql = 'UPDATE dat_spot_price SET creator=%(creator)s,price=%(price)s,src_note=%(src_note)s ' \
                 'WHERE spot_ts=%(spot_ts)s AND variety_en=%(variety_en)s;'
    update_count, _ = db.execute(update_sql, save_data, many=True)  # 再更新一波
    return AllResponse.operate_successfully(msg=f'保存现货价格数据成功,新增{count}个,更新{update_count}个.')


@spot_fill_api.get('/')  # 获取个人填报现货数据的品种及数据
async def get_spot_fill(ruizy_token: str = Header(...), date: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    query_ts = datetime_utils.auth_datetime_string(datetime_string=date, f='%Y%m%d', ts=True)
    if not query_ts:
        return AllResponse.validate_error()
    variety_sql = """
        SELECT a.variety_en,a.variety_name,a.spaces,b.expire  
        FROM ruizy_variety As a 
        LEFT JOIN ruizy_user_variety As b ON a.variety_en=b.variety_en AND b.user_id=%s AND b.name_space='V04';
    """
    variety_prm = (person['uid'],)
    db_conn = FAConnection()
    variety_ret = db_conn.query(variety_sql, variety_prm, keep_conn=True)
    # 过滤不属于现货空间的品种
    variety_ret = list(filter(lambda x: 'V04' in x['spaces'].split(','), variety_ret))
    if person['role_code'] in [constant.ADMIN_ROLE_CODE, constant.MANAGE_ROLE_CODE] or person['uid'] in [158]:
        auth_variety = {v['variety_en']: v['variety_name'] for v in variety_ret}
    else:
        now_day = datetime.date.today()
        auth_variety = {v['variety_en']: v['variety_name'] for v in variety_ret if
                        v['expire'] and v['expire'] >= now_day}

    # 查询现货数据
    spot_sql = 'SELECT yt.variety_en,yt.src_note As yt_note,yt.variety_name,tt.price,tt.src_note FROM ' \
               '(SELECT a.variety_en,a.src_note,b.variety_name ' \
               'FROM dat_spot_price As a INNER JOIN ruizy_variety As b ON a.variety_en=b.variety_en ' \
               'WHERE a.spot_ts=' \
               '(SELECT MAX(spot_ts) FROM dat_spot_price WHERE spot_ts<(SELECT MAX(spot_ts) FROM dat_spot_price))' \
               ') As yt LEFT JOIN ' \
               '(SELECT variety_en,price,src_note FROM dat_spot_price WHERE spot_ts=%s) ' \
               'AS tt ON yt.variety_en=tt.variety_en;'
    records = db_conn.query(spot_sql, (query_ts,))
    query_day = datetime.datetime.fromtimestamp(query_ts).strftime('%Y-%m-%d')
    response_data = []

    for item in records:
        item['spot_date'] = query_day
        if not item['price']:
            item['price'] = 0
        if not item['src_note']:
            item['src_note'] = item['yt_note']
        del item['yt_note']
        # 只要有权限的品种
        if item['variety_en'] in auth_variety.keys():
            response_data.append(item)

    # 有权限的品种，但现货表里没有的
    spot_variety = [v['variety_en'] for v in response_data]
    for v in set(auth_variety.keys()).difference(list(spot_variety)):
        response_data.append({
            'spot_date': query_day,
            'variety_en': v,
            'variety_name': auth_variety.get(v),
            'src_note': '',
            'price': 0
        })
    return AllResponse.operate_successfully(data=response_data)
